08. JDBC

JDBC API provides 3 different interfaces to execute the different types of SQL queries. They are,
Statement – Used to execute normal SQL queries.
PreparedStatement – Used to execute dynamic or parameterized SQL queries.
CallableStatement – Used to execute the stored procedures.

These three interfaces look very similar but they differ significantly from one another in the functionalities they provide and the performance they give.

Statement

Statement interface is used to execute normal SQL queries. You can’t pass the parameters to SQL query at run time using this interface. This interface is preferred over other two interfaces if you are executing a particular SQL query only once. Typically, Statement is used for DDL statements like CREATE, ALTER, DROP etc. For example,

//Creating The Statement Object
Statement stmt = con.createStatement();

//Executing The Statement
stmt.executeUpdate("CREATE TABLE STUDENT(ID NUMBER NOT NULL, NAME VARCHAR)");

PreparedStatement

PreparedStatement is used to execute dynamic or parameterized SQL queries. PreparedStatement extends Statement interface. You can pass the parameters to SQL query at run time using this interface. It is recommended to use PreparedStatement if you are executing a particular SQL query multiple times. It gives better performance than Statement interface. Because PreparedStatements are precompiled and the query plan is created only once irrespective of how many times you are executing that query.

//Creating PreparedStatement object
PreparedStatement pstmt = con.prepareStatement("update STUDENT set NAME = ? where ID = ?");

//Setting values to place holders using setter methods of PreparedStatement object
pstmt.setString(1, "MyName");   //Assigns "MyName" to first place holder
pstmt.setInt(2, 111);     //Assigns "111" to second place holder

//Executing PreparedStatement
pstmt.executeUpdate();

CallableStatement

CallableStatement is used to execute the stored procedures. A stored procedure is a group of one or more database statements stored in the database.

JDBC Transaction let you control how and when a transaction should commit into database.

//transaction block start

//SQL insert statement
//SQL update statement 
//SQL delete statement

//transaction block end

See below two examples to understand how JDBC transaction works.

Without Transaction

By default, data will be committed into database when executeUpdate() is called.

String insertTableSQL = "INSERT INTO DBUSER"
            + "(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) VALUES"
            + "(?,?,?,?)";

String updateTableSQL = "UPDATE DBUSER SET USERNAME =? "
            + "WHERE USER_ID = ?";

ps = getConnection().prepareStatement(insertTableSQL);
ps.setInt(1, 100);
ps.setString(2, "jdoe");
ps.setString(3, "app");
ps.setTimestamp(4, getCurrentTimeStamp());
ps.executeUpdate(); // data is committed once this method returns.

psu = getConnection().prepareStatement(updateTableSQL);
psu.setString(1, "A very very long string that will cause an error"); 
psu.setInt(2, 999);

psu.executeUpdate(); //Error, value too big

When this code is executed, the USER_ID = 100 is inserted but the username is not updated.

Transaction

To put this in a transaction, you can use
connection.setAutoCommit(false) to start a transaction block.
connection.commit() to successfully commit or end a transaction block.
connection.rollback() to rollback the transaction.

dbConnection.setAutoCommit(false); //transaction block start

String insertTableSQL = "INSERT INTO DBUSER"
            + "(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) VALUES"
            + "(?,?,?,?)";

String updateTableSQL = "UPDATE DBUSER SET USERNAME =? "
            + "WHERE USER_ID = ?";

ps = getConnection().prepareStatement(insertTableSQL);
ps.setInt(1, 100);
ps.setString(2, "jdoe");
ps.setString(3, "app");
ps.setTimestamp(4, getCurrentTimeStamp());
ps.executeUpdate(); // this UPDATE is not committed

psu = dbConnection.prepareStatement(updateTableSQL);
psu.setString(1, "A very very long string that will cause an error"); 
psu.setInt(2, 999);
psu.executeUpdate(); //Error, rollback, including the first insert statement.

dbConnection.commit(); //transaction block end

When executeUpdate() errors both the INSERT and UPDATE are rolled back.